package toko.service.impl;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Date;

import toko.domain.Buku;
import toko.domain.Penjualan;
import toko.domain.PenjualanDetail;
import toko.service.TokoService;

public class TokoServiceJDBC implements TokoService{
	private static final String sqlInsert="INSERT INTO produk (kode, judul, pengarang, penerbit, harga)" +
	"VALUES (?,?,?,?,?)";
	private static final String sqlInsertPenjualan="INSERT INTO penjualan (waktu, kasir, pembayaran)" +
	"VALUES (?,?,?)";
	
	private static final String sqlInsertPenjualanDetail="INSERT INTO penjualan_detail (id_penjualan, id_buku, qty)" +
	"VALUES (?,?,?)";
	private static final String sqlCariTglPenjualan="SELECT * FROM Penjualan WHERE date(waktu) BETWEEN date (?) AND date(?)";
	private static final String sqlCariJmlBuku="SELECT SUM(penjualan_detail.qty) AS jumlah FROM penjualan_detail " +
												" INNER JOIN produk ON penjualan_detail.id_buku = produk.id " + 
												" INNER JOIN penjualan ON penjualan_detail.id_penjualan = penjualan.id " + 
												" WHERE penjualan_detail.id_buku=? AND DATE(penjualan.waktu)BETWEEN DATE(?) AND DATE(?) " +
												" GROUP BY penjualan_detail.id_buku";
	
	private static final String sqlSelectId="SELECT * FROM produk WHERE id=? ";
	private static final String sqlSelectKode="SELECT * FROM produk WHERE kode like ?";
	private static final String sqlSelectJudul="SELECT * FROM produk WHERE judul like ?";
	private static final String sqlDeleteId="DELETE FROM produk WHERE kode ?";

	private Connection connection;
	private PreparedStatement psInsert;
	private PreparedStatement psInsertPenjualan;
	private PreparedStatement psInsertPenjualanDetail;
	private PreparedStatement psCariTglPenjualan;
	private PreparedStatement psHitungJmlPenjualanBuku;
	private PreparedStatement psSelectById;
	private PreparedStatement psSelectByKode;
	private PreparedStatement psSelectByJudul;
	private PreparedStatement psDeleteById;
	
	public TokoServiceJDBC(Connection connection) throws Exception{
		this.connection = connection;
		this.psInsert = connection.prepareStatement(sqlInsert);
		this.psInsertPenjualan = connection.prepareStatement(sqlInsertPenjualan);
		this.psInsertPenjualanDetail = connection.prepareStatement(sqlInsertPenjualanDetail);
		this.psSelectById =connection.prepareStatement(sqlSelectId);
		this.psSelectByKode =connection.prepareStatement(sqlSelectKode);
		this.psSelectByJudul =connection.prepareStatement(sqlSelectJudul);
		this.psDeleteById =connection.prepareStatement(sqlDeleteId);
		this.psCariTglPenjualan = connection.prepareStatement(sqlCariTglPenjualan);
		this.psHitungJmlPenjualanBuku = connection.prepareStatement(sqlCariJmlBuku);
	}
	
	@Override
	public Buku cariId(Integer id) {
		// TODO Auto-generated method stub
		try{
			psSelectById.setInt(1,id);
			ResultSet rs =psSelectById.executeQuery();
			
			//kalau next false, berarti tidak ada record yang ditemukan
			if(!rs.next()) return null;
			
			Buku b = konversiResultSetMenjadiBuku(rs);
			return b;
		}catch (Exception e){
			e.printStackTrace();
			return null;
		}
	}
	private Buku konversiResultSetMenjadiBuku(ResultSet rs) throws Exception{
		// TODO Auto-generated method stub
		Buku b = new Buku();
		b.setId(rs.getInt("id"));
		b.setKode(rs.getString("kode"));
		b.setJudul(rs.getString("judul"));
		b.setPenerbit(rs.getString("penerbit"));
		b.setPengarang(rs.getString("pengarang"));
		b.setHarga(rs.getBigDecimal("harga"));
		return b;
	}
	@Override
	public List<Penjualan> cariPenjualan(Date mulai,Date sampai) {
		// TODO Auto-generated method stub
		try{
			psCariTglPenjualan.setDate(1,new java.sql.Date(mulai.getDate()));
			psCariTglPenjualan.setDate(2,new java.sql.Date(sampai.getDate()));
			ResultSet rs =psCariTglPenjualan.executeQuery();
			if(!rs.next()) throw new IllegalStateException("penjualan tidak ditemukan");
			
			List<Penjualan> hasilPenjualan;
			hasilPenjualan = new ArrayList<Penjualan>();
			while (rs.next()){
				Penjualan p = konversiResultSetMenjadiPenjualan(rs);
				hasilPenjualan.add(p);
			}
			
			return hasilPenjualan;
		}catch (Exception e){
			e.printStackTrace();
			return null;
		}
	}
	private Penjualan konversiResultSetMenjadiPenjualan(ResultSet rs) throws Exception{
		// TODO Auto-generated method stub
		Penjualan pj = new Penjualan();
		pj.setId(rs.getInt("id"));
		pj.setWaktu(rs.getDate("waktu"));
		pj.setKasir(rs.getString("kasir"));
		pj.setPembayaran(rs.getBigDecimal("harga"));
		return pj;
	}
	
	@Override
	public Integer jmlBukuTerjual(Buku b, Date mulai, Date sampai) {
		// TODO Auto-generated method stub
		try{
			psHitungJmlPenjualanBuku.setInt(1, b.getId());
			psHitungJmlPenjualanBuku.setDate(2,new java.sql.Date(mulai.getDate()));
			psHitungJmlPenjualanBuku.setDate(3,new java.sql.Date(sampai.getDate()));
			
			ResultSet rs =psCariTglPenjualan.executeQuery();
			Integer jumlah = rs.getInt(1);
			if(!rs.next()) throw new IllegalStateException("penjualan tidak ditemukan");
			return jumlah;
			
		}catch (Exception e){
			e.printStackTrace();
			return null;
		}
	}
	
	@Override
	public void simpan(Buku buku) {
		// TODO Auto-generated method stub
		try{
			psInsert.setString(1, buku.getKode());
			psInsert.setString(2, buku.getJudul());
			psInsert.setString(3, buku.getPengarang());
			psInsert.setString(4, buku.getPenerbit());
			psInsert.setBigDecimal(5, buku.getHarga());
			psInsert.executeUpdate();
		}catch (SQLException e){
			e.printStackTrace();
		}
	}

	@Override
	public void simpanPenjualan(Penjualan penjualan) {
		// TODO Auto-generated method stub
		try{
			connection.setAutoCommit(false);
			psInsertPenjualan.setDate(1,new java.sql.Date(penjualan.getWaktu().getTime()));
			psInsertPenjualan.setString(2, penjualan.getKasir());
			psInsertPenjualan.setBigDecimal(3, penjualan.getPembayaran());
			psInsertPenjualan.executeUpdate();
			
			ResultSet rs = connection.createStatement().executeQuery("SELECT LAST_INSERT_ID()");
			if(!rs.next()) throw new IllegalStateException("ID ga d temuin");
			Integer id = rs.getInt(1);
			
			for(PenjualanDetail d : penjualan.getDaftarPenjualanDetail()){
				psInsertPenjualanDetail.setInt(1,id);
				psInsertPenjualanDetail.setInt(2,d.getBuku().getId());
				psInsertPenjualanDetail.setInt(3,d.getQuantity());
				psInsertPenjualanDetail.executeUpdate();
			}
			connection.commit();
		}catch (SQLException e){
			e.printStackTrace();
		}
	}

	@Override
	public void hapus(Buku buku) {
		// TODO Auto-generated method stub
		try{
			psDeleteById.setString(1, buku.getKode());
			psDeleteById.executeQuery();
		}catch (SQLException e){
			e.printStackTrace();
		}
	}
	
	@Override
	public void importCSV(String filename) {
		try {
			connection.setAutoCommit(false);
			BufferedReader br = new BufferedReader(new FileReader(new File(filename)));
			String line;
			while ((line = br.readLine()) != null) {
				String[] lines = line.split(";");
				psInsert.setString(1, lines[0]);
				psInsert.setString(2, lines[1]);
				psInsert.setString(3, lines[2]);
				psInsert.setString(4, lines[3]);
				psInsert.setBigDecimal(5,new BigDecimal(lines[4]));
				psInsert.executeUpdate();
			}
			br.close();
			connection.commit();
		} catch (Exception e) {
			try{
				System.out.println("Roll back, terjadi error");
				connection.rollback();
			}catch (SQLException e1){
				e.printStackTrace();
			}
		}finally{
			try{
				connection.setAutoCommit(true);
			}catch (SQLException e2) {
				// TODO: handle exception
				e2.printStackTrace();
			}
		}
	}

	@Override
	public List<Buku> cariKode(String kode) {
		// TODO Auto-generated method stub
		try{
			List<Buku> hasil = new ArrayList<Buku>(); 
			psSelectByKode.setString(1, "%"+kode+"%");
			ResultSet rs =psSelectByKode.executeQuery();
			while(rs.next()){
				Buku b = konversiResultSetMenjadiBuku(rs);
				hasil.add(b);
			}
			return hasil;
		}catch (Exception e){
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List<Buku> cariJudul(String judul) {
		// TODO Auto-generated method stub
		try{
			List<Buku> hasil = new ArrayList<Buku>(); 
			psSelectByJudul.setString(1, "%"+judul+"%");
			ResultSet rs =psSelectByJudul.executeQuery();
			while(rs.next()){
				Buku b = konversiResultSetMenjadiBuku(rs);
				hasil.add(b);
			}
			return hasil;
		}catch (Exception e){
			e.printStackTrace();
		}
		return null;
	}
}
